package com.android.nextBus.DB;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.Cursor;
import android.database.SQLException;

public class TransitDatabaseHelper extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "transitDB";
	
	// stop_times columns
	public static final String TRIP_ID="trip_id";
	public static final String ARRIVAL_TIME="arrival_time";
	static final String STOP_ID="stop_id";
	static final String STOP_SEQUENCE="stop_sequence";
	static final String PICKUP_TYPE="pickup_type";
	static final String DROP_OFF_TYPE="drop_off_type";
	
	// trips columns
	public static final String ROUTE_ID="route_id";
	static final String SERVICE_ID="service_id";
	static final String TRIP_HEADSIGN="trip_headsign";
	static final String BLOCK_ID="block_id";
	static final String SHAPE_ID="shape_id";
	
	// stops columns
	static final String STOP_NAME="stop_name";
	static final String STOP_DESC="stop_desc";
	static final String STOP_LAT="stop_lat";
	static final String STOP_LON="stop_lon";
	
	// routes columns
	static final String AGENCY_ID="agency_id";
	static final String ROUTE_SHORT_NAME="route_short_name";
	static final String ROUTE_LONG_NAME="route_long_name";
	static final String ROUTE_DESC="route_desc";
	static final String ROUTE_TYPE="route_type";
	
	// shapes columns
	static final String SHAPE_PT_LAT="shape_pt_lat";
	static final String SHAPE_PT_LON="shape_pt_lon";
	static final String SHAPE_PT_SEQUENCE="shape_pt_sequence";
	
	// calendar columns
	static final String MONDAY="monday";
	static final String TUESDAY="tuesday";
	static final String WEDNESDAY="wednesday";
	static final String THURSDAY="thursday";
	static final String FRIDAY = "friday";
	static final String SATURDAY = "saturday";
	static final String SUNDAY = "sunday";
	static final String START_DATE = "start_date";
	static final String END_DATE= "end_date";
	
	// calendar_dates columns
	static final String DATE = "date";
	static final String EXCEPTION_TYPE = "exception_type";
	
	public TransitDatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, 1);
	}

	@Override
	public void onCreate(SQLiteDatabase transitDB) {
		// TODO Auto-generated method stub
		transitDB.execSQL("create table stop_times(_id integer primary key autoincrement, trip_id text," +
				"arrival_time text, stop_id integer, stop_sequence integer, pickup_type integer," +
				"drop_off_type integer)");
		
		transitDB.execSQL("create table trips(_id integer primary key autoincrement, route_id text," +
				"service_id, trip_id text, trip_headsign text, block_id integer," +
				"shape_id integer)");
		
		transitDB.execSQL("create table stops(_id integer primary key autoincrement," +
				"stop_id integer, stop_name text, stop_desc text, stop_lat real," + 
				"stop_lon real)");
		
		transitDB.execSQL("create table routes(_id integer primary key autoincrement," +
				"route_id text, agency_id integer, route_short_name integer," +
				"route_long_name text, route_desc text, route_type integer)");
		
		transitDB.execSQL("create table shapes(_id integer primary key autoincrement," +
				"shape_id integer,shape_pt_lat real, shape_pt_lon real," + 
				"shape_pt_sequence integer)");
		
		transitDB.execSQL("create table calendar(_id integer primary key autoincrement," +
				"service_id text, monday integer, tuesday integer, wednesday integer," +
				"thursday integer, friday integer, saturday integer, sunday integer," + 
				"start_date integer, end_date integer)");
		
		transitDB.execSQL("create table calendar_dates(_id integer primary key autoincrement," +
				"service_id text, date integer, exception_type integer)");
		

		// preloaded data for testing
		ContentValues cv = new ContentValues();
		ContentValues cv2 = new ContentValues();
		ContentValues cv3 = new ContentValues();

		cv.put(TRIP_ID, "4859955-MAR12-Multi-Weekday-01");
		cv.put(ARRIVAL_TIME, "16:32:00");
		cv.put(STOP_ID, 53218);
		cv.put(STOP_SEQUENCE, 1);
		cv.put(PICKUP_TYPE, 1);
		cv.put(DROP_OFF_TYPE, 1);
		transitDB.insert("stop_times", STOP_ID, cv);
		
		cv.put(TRIP_ID, "4859964-MAR12-Multi-Weekday-01");
		cv.put(ARRIVAL_TIME, "15:35:00");
		cv.put(STOP_ID, 53218);
		cv.put(STOP_SEQUENCE, 1);
		cv.put(PICKUP_TYPE, 1);
		cv.put(DROP_OFF_TYPE, 1);
		transitDB.insert("stop_times", STOP_ID, cv);
		
		cv.put(TRIP_ID, "4859965-MAR12-Multi-Weekday-01");
		cv.put(ARRIVAL_TIME, "16:04:00");
		cv.put(STOP_ID, 53218);
		cv.put(STOP_SEQUENCE, 1);
		cv.put(PICKUP_TYPE, 1);
		cv.put(DROP_OFF_TYPE, 1);
		transitDB.insert("stop_times", STOP_ID, cv);
		
		cv.put(TRIP_ID, "4859966-MAR12-Multi-Weekday-01");
		cv.put(ARRIVAL_TIME, "17:14:00");
		cv.put(STOP_ID, 53218);
		cv.put(STOP_SEQUENCE, 1);
		cv.put(PICKUP_TYPE, 1);
		cv.put(DROP_OFF_TYPE, 1);
		transitDB.insert("stop_times", STOP_ID, cv);
		
		cv2.put(ROUTE_ID, "780-56");
		cv2.put(SERVICE_ID,"MAR12-Multi-Weekday-01");
		cv2.put(TRIP_ID,"4859955-MAR12-Multi-Weekday-01");
		cv2.put(TRIP_HEADSIGN,"North 780  Trip 3/Maple Grove/Shepherd Grove");
		cv2.put(BLOCK_ID,2193);
		cv2.put(SHAPE_ID,7800001);
		transitDB.insert("trips", TRIP_ID, cv2);
		
		cv2.put(ROUTE_ID, "780-56");
		cv2.put(SERVICE_ID, "MAR12-Multi-Weekday-01");
		cv2.put(TRIP_ID, "4859964-MAR12-Multi-Weekday-01");
		cv2.put(TRIP_HEADSIGN, "North 780  Trip 1/Maple Grove / Shepherd Grove");
		cv2.put(BLOCK_ID, 2191);
		cv2.put(SHAPE_ID, 7800001);
		transitDB.insert("trips", TRIP_ID, cv2);
		
		cv2.put(ROUTE_ID, "780-56");
		cv2.put(SERVICE_ID,"MAR12-Multi-Weekday-01");
		cv2.put(TRIP_ID, "4859965-MAR12-Multi-Weekday-01");
		cv2.put(TRIP_HEADSIGN, "North 780  Trip 2/Maple Grove/Shepherd Grove");
		cv2.put(BLOCK_ID, 2192);
		cv2.put(SHAPE_ID, 7800001);
		transitDB.insert("trips", TRIP_ID, cv2);
		
		cv2.put(ROUTE_ID, "780-56");
		cv2.put(SERVICE_ID,"MAR12-Multi-Weekday-01");
		cv2.put(TRIP_ID, "4859966-MAR12-Multi-Weekday-01");
		cv2.put(TRIP_HEADSIGN, "North 780  Trip 4/Maple Grove/Shepherd Grove");
		cv2.put(BLOCK_ID,2227);
		cv2.put(SHAPE_ID, 7800001);
		transitDB.insert("trips", TRIP_ID, cv2);
		
		cv3.put(STOP_ID, 53218);
		cv3.put(STOP_NAME,"16 St - Layover & 1 Av / 3 Av S");
		cv3.put(STOP_DESC,"Mid block E");
		cv3.put(STOP_LAT,44.967060);
		cv3.put(STOP_LON, -93.274239);
		transitDB.insert("stops", STOP_ID, cv3);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
		// likely should be something else this is just simple route until
		// decision is made
		android.util.Log.w("NextBus", "Uprgrading database, which will destroy" +
				"all old data");
		db.execSQL("drop table if exists trips");
		db.execSQL("drop table if exists stops");
		db.execSQL("drop table if exists stop_times");
		db.execSQL("drop table if exists shapes");
		db.execSQL("drop table if exists routes");
		db.execSQL("drop table if exists calendar");
		db.execSQL("drop table if exists calendar_dates");
		onCreate(db);
		
	}
}
